package br.com.millenium.domain.manager;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.persistence.Query;

import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.manager.ManagerFactory;
import lumis.util.ITransaction;

public class ClienteManager implements IClienteManager {

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDeleteableGrupo(java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDuplicateGrupo(String grupoNome, String grupoId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_Grupo WHERE nome = ? and id <> ?" );
			try
			{
				statement.setString(1, grupoNome);
				statement.setString(2, grupoId);
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDeleteableGrupo(java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDeleteableGrupo(String grupoId, ITransaction transaction) throws PortalException 
	{
		boolean returnValue = false;
		
		try 
		{
			String sql = "SELECT COUNT(id) AS total FROM MM_Bandeira WHERE grupoId = ?";
			Query query = ManagerFactory.getEntityManager().createNativeQuery(sql);
			query.setParameter(1, grupoId);
			
			BigInteger result  = (BigInteger) query.getSingleResult();
			returnValue = (result.intValue() == 0);
		}
		catch (Exception e) 
		{
			throw new UnexpectedException(e);
		}
		
		return returnValue;
	}

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDuplicateBandeira(java.lang.String, java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDuplicateBandeira(String bandeiraNome, String bandeiraId, ITransaction transaction) throws PortalException 
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_Bandeira WHERE nome = ? and id <> ?" );
			try
			{
				statement.setString( 1, bandeiraNome );
				statement.setString( 2, bandeiraId   );
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDuplicateFilial(java.lang.String, java.lang.String, java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDuplicateFilial(String filialId, String filialCodigo, String filialCNPJ, ITransaction transaction) throws PortalException 
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_Filial WHERE codigoFilial = ? and id <> ? and cnpj = ?" );
			try
			{
				statement.setString( 1, filialCodigo );
				statement.setString( 2, filialId     );
				statement.setString( 3, filialCNPJ   );
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDeleteableBandeira(java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDeleteableBandeira(String bandeiraId, ITransaction transaction) throws PortalException
	{
		boolean returnValue = false;
		
		try 
		{
			String sql = "SELECT COUNT(id) AS total FROM MM_Filial WHERE bandeiraId = ?";
			Query query = ManagerFactory.getEntityManager().createNativeQuery(sql);
			query.setParameter(1, bandeiraId);
			
			BigInteger result = (BigInteger) query.getSingleResult();
			returnValue = (result.intValue() == 0);
		}
		catch (Exception e) 
		{
			throw new UnexpectedException(e);
		}
		
		return returnValue;
	}

	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDuplicateOS(java.lang.String, java.lang.String, java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDuplicateOSFilial(String osId, String osNumero, String osFilial, ITransaction transaction) throws PortalException 
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_OS WHERE numeroOs = ? and id <> ? and filialId = ?" );
			try
			{
				statement.setString( 1, osNumero );
				statement.setString( 2, osId     );
				statement.setString( 3, osFilial );
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDuplicateOSEquipe(java.lang.String, java.lang.String, java.lang.String, lumis.util.ITransaction)
	 */
	@Override
	public boolean isDuplicateOSEquipe(String osId, String osNumero, String osEquipe, ITransaction transaction) throws PortalException 
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_OS WHERE numeroOs = ? and id <> ? and equipeId = ?" );
			try
			{
				statement.setString( 1, osNumero );
				statement.setString( 2, osId     );
				statement.setString( 3, osEquipe );
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see br.com.millenium.domain.manager.IClienteManager#isDuplicateOcorrencia(java.lang.String, java.lang.String, java.lang.String, java.lang.String, lumis.util.ITransaction)
	 */
	public boolean isDuplicateOcorrencia( String filialId, String tpOcorrencia, Date dtOcorrencia, String ocorrenciaId, ITransaction transaction ) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection       connection         = daoTransactionJdbc.getConnection();
			
			PreparedStatement statement = connection.prepareStatement( "SELECT id FROM MM_Ocorrencia WHERE filialId = ? and tpOcorrencia = ? and dtOcorrencia = ? and id <> ?" );
			try
			{
				statement.setString( 1, filialId     );
				statement.setString( 2, tpOcorrencia );
				statement.setDate  ( 3, dtOcorrencia );
				statement.setString( 4, ocorrenciaId );
				
				ResultSet rs = statement.executeQuery();
				if( rs.next() )
					return true;
				else
					return false;
			}
			finally
			{
				statement.close();
			}
		}
		catch (Exception e)
		{
			throw new PortalException(e.getMessage());
		}
	}
}
